﻿using BaseUtils.Logger;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;

namespace DatabaseCore.Util
{
    public class ParseSql
    {

        //string test = "SELECT column1, column2, column3 FROM table_name WHeRE column1 > 10 AND column2 = 'value' GROUP BY column3 ORDER BY column1 DESC LIMIT 10 OFFSET 20;";
        //public static string test = "select sPur, COUNT(*) AS total, column1 aS col1, MAX(column2) as maxCol2 FROM table_name WHeRE column1 > 10 AnD column2 = 'value' or  column3 != '2' GROuP BY column3 ORDEr BY column1 DESC LImIT 10 , 20;";
        public static string test = "select sPur, COUNT(*) AS total, column1 aS col1, MAX(column2) as maxCol2 FROM table_name WHeRE column1 > 10 AnD column2 = 'value' or  column3 != '2' GROuP BY column3 ORDEr BY column1 DESC LImIT 10 OffseT 20;";
        //public static string test = "select sPur, COUNT(*) AS total, column1 aS col1, MAX(column2) as maxCol2 FROM table_name WHeRE column1 > 10 AnD column2 = 'value' or  column3 != '2';";
        public string SQL { get; set; }

        public string Operation { get; set; }

        public string TableName { get; set; }

        public List<List<string>> Columns { get; set; }

        public string Wheres { get; set; }

        public List<WhereClauseInfo> WhereClauses { get; set; } = new List<WhereClauseInfo>();

        public string GroupByColumns { get; set; }

        public string OrderByColumns { get; set; }

        public int Limit { get; set; }

        public int Offset { get; set; }

        public ParseSql(string sql)
        {
            if (string.IsNullOrEmpty(sql)) {
                LoggerService.Error("sql不能为空");
            }
            SQL = sql;
            bool isRowNumberPaging = false;
            string rowNumberPagingCondition = "";

            Match rowNumberMatch = Regex.Match(sql, @"(?i)WHERE\s+.*ROW_NUMBER\(\)\s+OVER\(\s*\(\s*.*\s*\)\s*\)\s+(.*)");
            if (rowNumberMatch.Success)
            {
                isRowNumberPaging = true;
                rowNumberPagingCondition = rowNumberMatch.Groups[1].Value;
            }

            //Console.WriteLine($"操作类型: {Operation}");
            //Console.WriteLine($"表名: {TableName}");
            //Console.WriteLine($"操作字段: {Columns}");
            //Console.WriteLine($"条件: {Wheres}");
            //Console.WriteLine($"分组字段: {GroupByColumns}");
            //Console.WriteLine($"排序字段: {OrderByColumns}");
            //Console.WriteLine($"分页限制: {Limit}");
            //Console.WriteLine($"分页偏移: {Offset}");
            //Console.WriteLine($"是否使用 ROW_NUMBER 分页: {isRowNumberPaging}");
            //Console.WriteLine($"ROW_NUMBER 分页条件: {rowNumberPagingCondition}");
        }

        /// <summary>
        /// 判断操作类型
        /// </summary>
        /// <returns></returns>
        public ParseSql GetOperation()
        {
            // 判断操作类型
            if (SQL.Trim().StartsWith("SELECT", StringComparison.OrdinalIgnoreCase))
            {
                Operation = "SELECT";
            }
            else if (SQL.Trim().StartsWith("INSERT", StringComparison.OrdinalIgnoreCase))
            {
                Operation = "INSERT";
            }
            else if (SQL.Trim().StartsWith("UPDATE", StringComparison.OrdinalIgnoreCase))
            {
                Operation = "UPDATE";
            }
            else if (SQL.Trim().StartsWith("DELETE", StringComparison.OrdinalIgnoreCase))
            {
                Operation = "DELETE";
            }
            else if (SQL.Trim().StartsWith("ALTER", StringComparison.OrdinalIgnoreCase))
            {
                Operation = "ALTER";
            }
            return this;
        }

        public ParseSql GetTableName()
        {

            // 提取表名
            Match tableMatch = Regex.Match(SQL, @"(?i)(FROM|INTO|UPDATE)\s+([\w]+)");
            if (tableMatch.Success)
            {
                TableName = tableMatch.Groups[2].Value;
            }

            return this;
        }

        public ParseSql GetColumn()
        {
            // 提取列名（仅适用于 SELECT 和 UPDATE）

            // 提取列名（仅适用于 SELECT 和 UPDATE）
            if (Operation == "SELECT" || Operation == "UPDATE")
            {
                Match columnsMatch = Regex.Match(SQL, @"(?i)(SELECT|UPDATE)\s+([\w\s,\*\(\)]+(?:\s+(?i:AS)\s+\w+)?)\s+(FROM|SET)");
                List<List<string>> Target = new List<List<string>>();
                if (columnsMatch.Success)
                {
                    string columnsStr = columnsMatch.Groups[2].Value;
                    string[] _columnsStr = columnsStr.Split(',');

                    foreach (string column in _columnsStr)
                    {
                        List<string> Item = new List<string>();
                        Item.Add(column);
                        string trimmedColumn = column.Trim();
                        Match aliasMatch = Regex.Match(trimmedColumn, @"(.*)\s+(?i:AS)\s+(.*)");
                        if (aliasMatch.Success)
                        {
                            //Console.WriteLine($"{aliasMatch.Groups[2].Value} (原始表达式: {aliasMatch.Groups[1].Value})");
                            Item.Add(aliasMatch.Groups[1].Value);
                            Item.Add(aliasMatch.Groups[2].Value);
                        }
                        else
                        {
                            //Console.WriteLine(trimmedColumn);
                            Item.Add(trimmedColumn);
                            Item.Add(trimmedColumn);
                        }
                        Target.Add(Item);
                    }

                }
                Columns = Target;
            }

            return this;
        }

        public ParseSql GetWhere()
        {

            // 提取条件（仅适用于有条件的语句）
            if (SQL.ToLower().Contains("where"))
            {
                //Match conditionsMatch = Regex.Match(SQL, @"(?i)WHERE\s+(.*)");
                // Match conditionsMatch = Regex.Match(SQL, @"(?i)WHERE\s+(.*?)(?=\s+(SELECT|INSERT|UPDATE|DELETE|FROM|GROUP BY|LIMIT|ORDER BY|;$))");
                //Match conditionsMatch = Regex.Match(SQL, @"(?i)WHERE\s+(.*?)(?=\s+(GROUP BY|LIMIT|ORDER BY+(?:\s+(?:SELECT|INSERT|UPDATE|DELETE|FROM))?|$))");

                string pattern = @"WHERE.*?(?=(?:SELECT|INSERT|UPDATE|DELETE|FROM|GROUP BY|LIMIT|ORDER BY|HAVING|;))";
                Match conditionsMatch = Regex.Match(SQL, pattern, RegexOptions.IgnoreCase);
                //Wheres = match.Success ? match.Value : null;
                if (conditionsMatch.Success)
                {
                    Wheres = conditionsMatch.Value;
                    Wheres = !string.IsNullOrEmpty(Wheres) ? Regex.Replace(Wheres, "where", "", RegexOptions.IgnoreCase) : null;
                }
            }
            ParseWhereConditions();
            return this;
        }

        private void ParseWhereConditions()
        {
            string whereCondition = Wheres;
            string[] andOrKeywords = { "and", "or" };
            List<string> conditions = new List<string>();

            while (!string.IsNullOrEmpty(whereCondition))
            {
                bool foundKeyword = false;
                foreach (string keyword in andOrKeywords)
                {
                    int keywordIndex = whereCondition.IndexOf(keyword, StringComparison.OrdinalIgnoreCase);
                    if (keywordIndex != -1)
                    {
                        foundKeyword = true;
                        string conditionPart = whereCondition.Substring(0, keywordIndex).Trim();
                        conditions.Add(conditionPart);
                        whereCondition = whereCondition.Substring(keywordIndex + keyword.Length).Trim();
                        break;
                    }
                }
                if (!foundKeyword)
                {
                    conditions.Add(whereCondition);
                    whereCondition = "";
                }
            }

            foreach (string condition in conditions)
            {
                Match fieldMatch = Regex.Match(condition, @"\b(\w+)\b");
                Match conditionMatch = Regex.Match(condition, @"(=|<>|>|<|>=|<=|!=)");
                Match valueMatch = Regex.Match(condition, @"(?<=(=|<>|>|<|>=|<=|!=))\s*('.*?'|\d+)");

                if (fieldMatch.Success && conditionMatch.Success && valueMatch.Success)
                {
                    WhereClauses.Add(new WhereClauseInfo
                    {
                        Field = fieldMatch.Value,
                        Condition = conditionMatch.Value,
                        Value = valueMatch.Value
                    });
                }
            }
        }

        public ParseSql GetGroupBy()
        {
            // 提取分组信息
            if (SQL.Contains("GROUP BY"))
            {
                //Match groupByMatch = Regex.Match(SQL, @"(?i)GROUP BY\s+(.*)");
                Match groupByMatch = Regex.Match(SQL, @"(?i)GROUP BY\s+(.*?)(?=\s+(ORDER BY|LIMIT|WHERE|$))");
                if (groupByMatch.Success)
                {
                    GroupByColumns = groupByMatch.Groups[1].Value;
                }
            }

            return this;
        }

        public ParseSql GetOrderBy()
        {
            // 提取排序信息
            if (SQL.Contains("ORDER BY"))
            {
                //Match orderByMatch = Regex.Match(SQL, @"(?i)ORDER BY\s+(.*)");
                Match orderByMatch = Regex.Match(SQL, @"(?i)ORDER BY\s+(.*?)(?=\s+(LIMIT|WHERE|$))");
                if (orderByMatch.Success)
                {
                    OrderByColumns = orderByMatch.Groups[1].Value;
                }
            }
            return this;
        }

        public ParseSql GetLimit()
        {
            // 提取分页信息（处理 MySQL 的 LIMIT 和 OFFSET，以及 SQL Server 的 ROW_NUMBER）
            //Match limitMatch = Regex.Match(SQL, @"(?i)LIMIT\s+(\d+)");
            //if (limitMatch.Success)
            //{
            //    Limit = int.Parse(limitMatch.Groups[1].Value);
            //}
            Match limitMatch = Regex.Match(SQL, @"(?i)LIMIT\s+\d+(?:\s*,\s*\d+)?");
            if (limitMatch.Success)
            {
                string limitStr = limitMatch.Value;
                string[] parts = limitStr.Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
                if (parts.Length > 0)
                {
                    Limit = int.Parse(parts[0].ToLower().TrimStart("limit".ToCharArray()).Trim());
                    if (parts.Length > 1)
                    {
                        Offset = int.Parse(parts[1].Trim());
                    }
                }
            }
            return this;
        }

        public ParseSql GetOffset()
        {
            Match offsetMatch = Regex.Match(SQL, @"(?i)OFFSET\s+(\d+)");
            if (offsetMatch.Success)
            {
                Offset = int.Parse(offsetMatch.Groups[1].Value);
            }

            return this;
        }

    }

    public class WhereClauseInfo
    {
        public string Field { get; set; }
        public string Condition { get; set; }
        public string Value { get; set; }
    }
}
